﻿Imports System.Data.SqlClient

Public Class dbconnection

    ' Delcare private members
    Private Shared cn As SqlConnection = New SqlConnection
    Private Shared svr As String
    Private Shared db As String
    Private Shared ur As String
    Private Shared pwd As String

    ' Create Property to Read/Write data of ConnectionString
    ' To Read/Write for Server Name
    Public Shared Property ServerName() As String
        Get
            Return svr
        End Get
        Set(ByVal value As String)
            svr = value
        End Set
    End Property

    ' To Read/Write for Databse Name
    Public Shared Property Database() As String
        Get
            Return db
        End Get
        Set(ByVal value As String)
            db = value
        End Set
    End Property

    ' To Read/Write for User ID
    Public Shared Property UserID() As String
        Get
            Return ur
        End Get
        Set(ByVal value As String)
            ur = value
        End Set
    End Property

    ' To Read/Write for Password
    Public Shared Property Password() As String
        Get
            Return pwd
        End Get
        Set(ByVal value As String)
            pwd = value
        End Set
    End Property

    ' To Read info of SqlConnection
    Public Shared ReadOnly Property Connection() As SqlConnection
        Get
            Return cn
        End Get
    End Property

    ' To Configure Connection to Database (Server) 
    Public Shared Function OpenConnection() As Boolean
        Try
            ' cn.ConnectionString = "Server = Tola-PC; Database = Stock; UID = sa; PWD = 123"
            ServerName = "DIN-PC"
            Database = "SaleDb"
            UserID = "sa"
            Password = "123456"

            cn.ConnectionString = "Server = " & ServerName & _
                                  "; Database = " & Database & _
                                  "; UID = " & UserID & _
                                  "; PWD = " & Password
            ' Open Connection
            cn.Open()

            Return True
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return False
        End Try
    End Function

    Public Shared Sub ExecuteNonQuery(ByVal sql As String)
        Dim cmd As New SqlCommand(sql, cn)
        cmd.ExecuteNonQuery()
    End Sub
    Public Shared Function ExecuteScalar(ByVal sql As String) As Object
        Dim cmd As New SqlCommand(sql, cn)
        Dim val = cmd.ExecuteScalar
        Return val
    End Function
    Public Shared Function ExecuteDatatable(ByVal sql As String) As DataTable
        Dim cmd As New SqlCommand(sql, cn)
        Dim read = cmd.ExecuteReader
        Dim dt As New DataTable
        dt.Load(read)
        read.Close()
        Return dt
    End Function

End Class
